import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

pd.set_option('display.width', None)
# 读取数据
df = pd.read_excel(r'C:\pythondata\data1.xlsx')
# 数据类型的基本信息
print("数据类型的基本信息:\n")
print(df.info())
print("--------------------------------------------------------")

# 计算有关的dataframe列的统计信息摘要
print("计算有关的dataframe列的统计信息摘要:\n")
print(df.describe())
print("--------------------------------------------------------")
# 数据清洗
print('数据清洗：\n')
missing_values = df.isnull().sum()
if missing_values.any():
    mean_values = df.mean()
    df_filled = df.fillna(mean_values)
    missing_locations = df.isnull()
    missing_rows, missing_cols = missing_locations.any(axis=1), missing_locations.any(axis=0)
    if missing_rows.any() or missing_cols.any():
        print("找到缺失的值:")
        if missing_rows.any():
            print("缺少值的行:")
            print(df[missing_rows])
        if missing_cols.any():
            print("缺少值的列:")
            print(df.loc[:, missing_cols])
else:
    print("无缺失值")

# 保存处理后的数据
df.to_excel(r'C:\pythondata\data1.xlsx', index=False, engine='openpyxl')
print("-------------------------------------------------------")
## 数据转换
print("数据转换\n")
column_mapping = {
    'ID': '客户ID',
    'LIMIT_BAL': '信用额度',
    'SEX': '性别',
    'EDUCATION': '教育程度',
    'MARRIAGE': '婚姻状况',
    'AGE': '年龄',
    'PAY_0': '9月还款情况',
    'PAY_2': '8月还款情况',
    'PAY_3': '7月还款情况',
    'PAY_4': '6月还款情况',
    'PAY_5': '5月还款情况',
    'PAY_6': '4月还款情况',
    'BILL_AMT1': '9月账单金额',
    'BILL_AMT2': '8月账单金额',
    'BILL_AMT3': '7月账单金额',
    'BILL_AMT4': '6月账单金额',
    'BILL_AMT5': '5月账单金额',
    'BILL_AMT6': '4月账单金额',
    'PAY_AMT1': '9月以前支付金额',
    'PAY_AMT2': '8月以前支付金额',
    'PAY_AMT3': '7月以前支付金额',
    'PAY_AMT4': '6月以前支付金额',
    'PAY_AMT5': '5月以前支付金额',
    'PAY_AMT6': '4月以前支付金额',
    'default payment next month': '是否逾期'
}
sex_map = {1: 'boy', 2: 'girl'}
marriage_map = {1: 'married', 2: 'single', 3: 'others'}
pay_map = {-2: 'No consumption', -1: 'Full payment', 0: 'Only pay the minimum amount',
           1: 'Payment delay of one month', 2: 'Payment delay of two month',
           3: 'Payment delay of three month', 4: 'Payment delay of four month',
           5: 'Payment delay of five month', 6: 'Payment delay of six month', 7: 'Payment delay of seven month',
           8: 'Payment delay of eight month', 9: 'Payment delay of nine month'}
education_map = {1: 'graduate student', 2: 'university', 3: 'high school', 4: 'others', 5: 'unknown', 6: 'unknown'}
default_payment_next_month_map = {1: 'Yes', 0: 'No'}
df.rename(columns=column_mapping, inplace=True)
df['性别'] = df['性别'].map(sex_map)
df['教育程度'] = df['教育程度'].map(education_map)
df['婚姻状况'] = df['婚姻状况'].map(marriage_map)
df['9月还款情况'] = df['9月还款情况'].map(pay_map)
df['8月还款情况'] =df['8月还款情况'].map(pay_map)
df['7月还款情况'] = df['7月还款情况'].map(pay_map)
df['6月还款情况'] = df['6月还款情况'].map(pay_map)
df['5月还款情况'] = df['5月还款情况'].map(pay_map)
df['4月还款情况'] = df['4月还款情况'].map(pay_map)
df['是否逾期'] = df['是否逾期'].map(default_payment_next_month_map)

print(df)

# 绘制年龄分布直方图
plt.figure(figsize=(10, 6))
sns.histplot(df['年龄'], bins=30, kde=True)
plt.title('Age distribution histogram')
plt.xlabel('age')
plt.ylabel('frequency')
plt.show()

# 绘制教育程度饼图
education_counts = df['教育程度'].value_counts()
plt.figure(figsize=(8, 6))
plt.pie(education_counts, labels=education_counts.index, autopct='%1.1f%%')
plt.title('Education level pie chart')
plt.show()

print("--------------------------------------------------------")
# 计算不同婚姻状况下逾期占比
marriage_default_counts = df.groupby('婚姻状况')['是否逾期'].value_counts(normalize=True).mul(100).rename('百分比').reset_index()
marriage_default_counts['婚姻状况'] = marriage_default_counts['婚姻状况'].replace(marriage_map)
plt.figure(figsize=(10, 6))
sns.barplot(x='婚姻状况', y='百分比', hue='是否逾期', data=marriage_default_counts)
plt.title('The proportion of overdue payments under different marital statuses')
plt.xlabel('marital status')
plt.ylabel('Overdue proportion(%)')
plt.show()

print("--------------------------------------------------------")
